{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MySQL  \n",
    "\n",
    "MySQL教程 https://www.runoob.com/mysql/mysql-tutorial.html   \n",
    "使用Docker搭建MySQL服务 https://www.cnblogs.com/sablier/p/11605606.html  \n",
    "\n",
    "以MoviesLen 数据为示例 https://grouplens.org/datasets/movielens/  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Platform : win32 [win32/linux]\n",
      "Systerm  : 3.6.10 (default, Mar  5 2020, 10:17:47) [MSC v.1900 64 bit (AMD64)] \n",
      "pymysql  Version: 0.10.0\n",
      "pandas  Version: 1.0.4\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import sys\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "from prettytable import PrettyTable  # 图表展示插件\n",
    "\n",
    "print('Platform : {} [win32/linux]'.format(sys.platform))  # 当前平台信息\n",
    "print('Systerm  : {} '.format(sys.version))\n",
    "print('pymysql  Version: {}'.format(pymysql.__version__))\n",
    "print('pandas  Version: {}'.format(pd.__version__))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据库操作类\n",
    "class MySQL:\n",
    "    # 初始化 连接数据库\n",
    "    def __init__(self, host, port, user, password, database):\n",
    "        \"\"\"\n",
    "        :param host:     数据库IP地址\n",
    "        :param port:     数据库端口\n",
    "        :param user:     用户名\n",
    "        :param password: 用户密码\n",
    "        :param database: 数据库名称\n",
    "        \"\"\"\n",
    "        # 打开数据库连接\n",
    "        self.db = pymysql.connect(host=host, port=port, user=user, password=password)\n",
    "        # 使用 cursor() 方法创建一个游标对象 cursor\n",
    "        self.cursor = self.db.cursor()\n",
    "\n",
    "        # 创建数据库的sql(如果数据库存在就不创建，防止异常)\n",
    "        self.cursor.execute(\"CREATE DATABASE IF NOT EXISTS {}\".format(database))\n",
    "        self.cursor.execute(\"USE {};\".format(database))\n",
    "        print('Connection Successful.  Database Version: < {} >'.format(self.select_sql(\"SELECT VERSION();\")[0][0]))\n",
    "\n",
    "    # select 原代码 查询\n",
    "    def select_sql(self, sql_code):\n",
    "        \"\"\"\n",
    "        :param sql_code:   SQL 查询代码\n",
    "        :return:  返回所有查询结果\n",
    "        \"\"\"\n",
    "        try:\n",
    "            # 使用 execute()  方法执行 SQL 查询\n",
    "            self.cursor.execute(sql_code)\n",
    "            return self.cursor.fetchall()\n",
    "        except Exception as error:\n",
    "            return 'Error: \\n{}\\n{}'.format(sql_code, error)\n",
    "\n",
    "    # execute方法执行\n",
    "    def execute(self, sql_code):\n",
    "        try:\n",
    "            self.cursor.execute(sql_code)\n",
    "            return 'Success:\\n{}\\nEXECUTE Successfully!'.format(sql_code)\n",
    "        except Exception as error:\n",
    "            return 'Error:\\n{}\\nEXECUTE Error: {}'.format(sql_code, error)\n",
    "\n",
    "    # 查询数据表结构\n",
    "    def desc_table(self, table_name=None):\n",
    "        \"\"\"\n",
    "        :param table_name:  待查询的表格名称，若为 None 则循环查询数据库中所有表格的数据结构\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        if table_name is None:\n",
    "            table_name = [table_i[0] for table_i in self.select_sql(\"SHOW TABLES;\")]\n",
    "        else:\n",
    "            table_name = [table_name]\n",
    "\n",
    "        table_info = ''\n",
    "        for table_name_i in table_name:\n",
    "            sql_code = \"DESC {}\".format(table_name_i)  # Field  Type  Null  Key  Default  Extra\n",
    "\n",
    "            # 打印表格信息\n",
    "            table = PrettyTable()\n",
    "            table.field_names = [\"Field\", \"Type\", \"Null\", \"Key\", \"Default\", \"Extra\"]\n",
    "            table.align[\"Field\"] = \"l\"\n",
    "            table.padding_width = 1  # 打印表格信息\n",
    "\n",
    "            for t_i in self.select_sql(sql_code):\n",
    "                table.add_row(t_i)\n",
    "\n",
    "            # 查询记录数量\n",
    "            count = self.select_sql('SELECT COUNT(*) FROM {};'.format(table_name_i))\n",
    "            # 当前表格数据结构\n",
    "            table_info_i = 'Table < {} >  Count: < {} >  Information:\\n{}'.format(table_name_i, count[0][0], table)\n",
    "            # 合并到所有表格数据结构信息中\n",
    "            table_info = '{}\\n{}\\n'.format(table_info, table_info_i)\n",
    "\n",
    "        return table_info\n",
    "\n",
    "    # SQL 创建表格\n",
    "    def create_table(self, table_name, table_info):\n",
    "        \"\"\"\n",
    "        :param table_name:   创建表格名称\n",
    "        :param table_info:   创建表格各列信息\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        sql_code = 'CREATE TABLE if not exists  {} ({});'.format(table_name, table_info, table_name)\n",
    "\n",
    "        try:\n",
    "            # 执行sql语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "            return 'Success:\\n{}\\nCREATE Successfully!'.format(sql_code)\n",
    "        except Exception as error:\n",
    "            # 如果发生错误则回滚\n",
    "            self.db.rollback()\n",
    "            return 'Error:\\n{}\\nCREATE Error: {}'.format(sql_code, error)\n",
    "\n",
    "    # 删除表格\n",
    "    def drop_table(self, table_name):\n",
    "        sql_code = 'DROP TABLE {};'.format(table_name)\n",
    "\n",
    "        try:\n",
    "            # 执行sql语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "            return 'Success:\\n{}\\nDROP TABLE Successfully!'.format(sql_code)\n",
    "        except Exception as error:\n",
    "            # 如果发生错误则回滚\n",
    "            self.db.rollback()\n",
    "            return 'Error:\\n{}\\nDROP TABLE Error: {}'.format(sql_code, error)\n",
    "\n",
    "    # SQL 插入数据\n",
    "    def insert(self, table_name, values, keys=None):\n",
    "        \"\"\"\n",
    "        :param table_name:  表格名称\n",
    "        :param values:      列值 单条记录：[V1,V2,V3]    多条记录：[[V1,V2,V3],[V1,V2,V3],[V1,V2,V3]]\n",
    "        :param keys:        列名【若为None，则写入所有的列中】\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        # 插入多条记录标志\n",
    "        if isinstance(values[0], (list, tuple)):\n",
    "            # 将数值合并成SQL CODE\n",
    "            values_code = ''\n",
    "            for values_i in values:\n",
    "                values_code_j = ''\n",
    "                for values_i_j in values_i:\n",
    "                    if isinstance(values_i_j, (int, float)) or values_i_j in ('NOW()', 'CURTIME()'):\n",
    "                        values_code_j += \"{},\".format(values_i_j)\n",
    "                    elif values_i_j is None:\n",
    "                        values_code_j += \"'{}',\".format(values_i_j)\n",
    "                    else:\n",
    "                        values_code_j += \"{},\".format(repr(values_i_j))\n",
    "                values_code += \"({}),\".format(values_code_j[: -1])\n",
    "\n",
    "            if keys is None:\n",
    "                sql_code = \"INSERT INTO {} VALUES {};\".format(table_name, values_code[: -1])\n",
    "            else:\n",
    "                sql_code = \"INSERT INTO {} ({}) VALUES {};\".format(table_name, \",\".join(keys), values_code[: -1])\n",
    "\n",
    "        # 插入单条记录\n",
    "        else:\n",
    "            # 将数值合并成SQL CODE\n",
    "            values_code = ''\n",
    "            for values_i in values:\n",
    "                if isinstance(values_i, (int, float)) or values_i in ('NOW()', 'CURTIME()'):\n",
    "                    values_code += \"{},\".format(values_i)\n",
    "                elif values_i is None:\n",
    "                    values_code += \"'{}',\".format(values_i)\n",
    "                else:\n",
    "                    values_code += \"{},\".format(repr(values_i))\n",
    "\n",
    "            if keys is None:\n",
    "                sql_code = \"INSERT INTO {} VALUES ({});\".format(table_name, values_code[: -1])\n",
    "            else:\n",
    "                sql_code = \"INSERT INTO {} ({}) VALUES ({});\".format(table_name, \",\".join(keys), values_code[: -1])\n",
    "\n",
    "        try:\n",
    "            # 执行sql语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "            return 'Success:\\n{}\\nINSERT Successfully!'.format(sql_code)\n",
    "        except Exception as error:\n",
    "            # 如果发生错误则回滚\n",
    "            self.db.rollback()\n",
    "            return 'Error:\\n{}\\nINSERT Error: {}'.format(sql_code, error)\n",
    "\n",
    "    # SQL 更新数据\n",
    "    def update(self, table_name, update_code, condition_code):\n",
    "        \"\"\"\n",
    "        :param table_name:      更新表格名称\n",
    "        :param update_code:     更新数值语句\n",
    "        :param condition_code:  更新条件语句\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        sql_code = 'UPDATE {} SET {} WHERE {};'.format(table_name, update_code, condition_code)\n",
    "        try:\n",
    "            # 执行sql语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "            return 'Success:\\n{}\\nUPDATE Successfully!'.format(sql_code)\n",
    "        except Exception as error:\n",
    "            # 如果发生错误则回滚\n",
    "            self.db.rollback()\n",
    "            return 'Error:\\n{}\\nUPDATE Error: {}'.format(sql_code, error)\n",
    "\n",
    "    # SQL 删除数据\n",
    "    def delete(self, table_name, condition_code):\n",
    "        \"\"\"\n",
    "        :param table_name:      表格名称\n",
    "        :param condition_code:  删除条件语句\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        sql_code = 'DELETE FROM {} WHERE {};'.format(table_name, condition_code)\n",
    "        try:\n",
    "            # 执行sql语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            # 提交到数据库执行\n",
    "            self.db.commit()\n",
    "            return 'Success:\\n{}\\nDELETE Successfully!'.format(sql_code)\n",
    "        except Exception as error:\n",
    "            # 如果发生错误则回滚\n",
    "            self.db.rollback()\n",
    "            return 'Error:\\n{}\\nDELETE Error: {}'.format(sql_code, error)\n",
    "\n",
    "    # SQL 查询数据\n",
    "    def select(self, table_name, keys=None, condition_code=None, string_flag=False):\n",
    "        \"\"\"\n",
    "        :param table_name:     查询表格名称\n",
    "        :param keys:           查询列名，若为None 默认查询所有列\n",
    "        :param condition_code: 查询条件，若为None 默认查询所有数据\n",
    "        :param string_flag:    是否以字符串形式输出，True时，通过Prettytable 显示为表格数据\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        if keys is None:\n",
    "            key_code = '*'\n",
    "        else:\n",
    "            key_code = ','.join(keys)\n",
    "\n",
    "        if condition_code is None:\n",
    "            sql_code = \"SELECT {} FROM {};\".format(key_code, table_name)\n",
    "        else:\n",
    "            sql_code = \"SELECT {} FROM {} WHERE {};\".format(key_code, table_name, condition_code)\n",
    "\n",
    "        try:\n",
    "            # 执行sql语句\n",
    "            self.cursor.execute(sql_code)\n",
    "            select_data = self.cursor.fetchall()\n",
    "\n",
    "            # 转换成表格方便可视化\n",
    "            if string_flag:\n",
    "                # 打印表格信息\n",
    "                table = PrettyTable()\n",
    "                if keys is None:\n",
    "                    table.field_names = [columns[0] for columns in self.select_sql(\"DESC {}\".format(table_name))]\n",
    "                else:\n",
    "                    table.field_names = keys\n",
    "\n",
    "                table.align[\"Field\"] = \"l\"\n",
    "                table.padding_width = 1  # 打印表格信息\n",
    "\n",
    "                for s_i, select_data_i in enumerate(select_data):\n",
    "                    table.add_row(select_data_i)\n",
    "                    # 默认只显示指定行数 17+2\n",
    "                    if s_i > 17:\n",
    "                        table.add_row(['.'] * len(select_data[0]))\n",
    "                        break\n",
    "\n",
    "                select_data = 'Table < {} >  Count: < {} >  Values:\\n{}\\n'.format(\n",
    "                    table_name, len(select_data), table)\n",
    "\n",
    "            return select_data\n",
    "\n",
    "        except Exception as error:\n",
    "            return 'Error:\\n{}\\nSELECT Error: {}'.format(sql_code, error)\n",
    "\n",
    "    # 关闭数据库连接\n",
    "    def close(self):\n",
    "        # 关闭cursor\n",
    "        self.cursor.close()\n",
    "        # 关闭数据库\n",
    "        self.db.close()\n",
    "\n",
    "\n",
    "# 创建表格\n",
    "def create_table(db):\n",
    "    # 创建用户信息表格\n",
    "    users_table = [\"UserID INT UNSIGNED PRIMARY KEY\",\n",
    "                   \"Gender CHAR(1) NOT NULL\",\n",
    "                   \"Age TINYINT UNSIGNED\",\n",
    "                   \"OccupationID  TINYINT UNSIGNED \",\n",
    "                   \"ZipCode VARCHAR(20)   \"\n",
    "                   ]\n",
    "    print(db.create_table('users', ','.join(users_table)))\n",
    "\n",
    "    # 创建电影信息表格\n",
    "    movies_table = [\"MovieID INT UNSIGNED PRIMARY KEY\",\n",
    "                    \"Title VARCHAR(100) NOT NULL\",\n",
    "                    \"Genres VARCHAR(100) NOT NULL\"\n",
    "                    ]\n",
    "    print(db.create_table('movies', ','.join(movies_table)))\n",
    "\n",
    "    # 创建评分信息表格\n",
    "    ratings_table = [\"UserID INT UNSIGNED\",\n",
    "                     \"MovieID INT UNSIGNED\",\n",
    "                     \"ratings INT UNSIGNED\",\n",
    "                     \"timestamps INT UNSIGNED\"\n",
    "                     ]\n",
    "    print(db.create_table('ratings', ','.join(ratings_table)))\n",
    "\n",
    "    \n",
    "# # 连接数据库\n",
    "# db = MySQL(host='localhost', port=3306, user='root', password='123456', database=\"MoviesLen\")\n",
    "\n",
    "# print(db.desc_table())\n",
    "\n",
    "\n",
    "# # 以下为逐行导入，效率较低\n",
    "# if False:\n",
    "#     users_path = os.path.abspath(os.path.join(\n",
    "#         r'D:\\下载\\STUVincent_vincent-movie_recommender-master\\movie_recommender\\data\\ml-1m', 'users.dat'))\n",
    "\n",
    "#     users_title = ['UserID', 'Gender', 'Age', 'OccupationID', 'Zip-code']\n",
    "#     users = pd.read_table(users_path, sep='::', header=None, names=users_title, engine='python')\n",
    "\n",
    "#     # 创建用户信息表格\n",
    "#     users_table = [\"UserID INT UNSIGNED PRIMARY KEY\",\n",
    "#                    \"Gender CHAR(1) NOT NULL\",\n",
    "#                    \"Age TINYINT UNSIGNED\",\n",
    "#                    \"OccupationID  TINYINT UNSIGNED \",\n",
    "#                    \"ZipCode VARCHAR(20)   \"\n",
    "#                    ]\n",
    "#     print(db.create_table('users', ','.join(users_table)))\n",
    "\n",
    "#     table_keys = ['UserID', 'Gender', 'Age', 'OccupationID', 'ZipCode']\n",
    "\n",
    "#     # 插入数据\n",
    "#     for i in range(len(users)):\n",
    "#         table_values = list(users.loc[i][:].values)\n",
    "#         print(db.insert('users', values=table_values, keys=table_keys))\n",
    "\n",
    "# if False:\n",
    "#     movies_path = os.path.abspath(os.path.join(\n",
    "#         r'D:\\下载\\STUVincent_vincent-movie_recommender-master\\movie_recommender\\data\\ml-1m', 'movies.dat'))\n",
    "\n",
    "#     movies_title = ['MovieID', 'Title', 'Genres']\n",
    "#     movies = pd.read_table(movies_path, sep='::', header=None, names=movies_title, engine='python')\n",
    "\n",
    "#     # 创建用户信息表格\n",
    "#     movies_table = [\"MovieID INT UNSIGNED PRIMARY KEY\",\n",
    "#                     \"Title VARCHAR(50) NOT NULL\",\n",
    "#                     \"Genres VARCHAR(100) NOT NULL\"\n",
    "#                     ]\n",
    "#     print(db.create_table('movies', ','.join(movies_table)))\n",
    "\n",
    "#     # 插入数据\n",
    "#     for i in range(len(movies)):\n",
    "#         table_values = list(movies.loc[i][:].values)\n",
    "#         print(db.insert('movies', values=table_values, keys=movies_title))\n",
    "\n",
    "# if False:\n",
    "#     ratings_path = os.path.abspath(os.path.join(\n",
    "#         r'D:\\下载\\STUVincent_vincent-movie_recommender-master\\movie_recommender\\data\\ml-1m', 'ratings.dat'))\n",
    "\n",
    "#     ratings_title = ['UserID', 'MovieID', 'ratings', 'timestamps']\n",
    "#     ratings = pd.read_table(ratings_path, sep='::', header=None, names=ratings_title, engine='python')\n",
    "\n",
    "#     # 创建用户信息表格\n",
    "#     ratings_table = [\"UserID INT UNSIGNED\",\n",
    "#                      \"MovieID INT UNSIGNED\",\n",
    "#                      \"ratings INT UNSIGNED\",\n",
    "#                      \"timestamps INT UNSIGNED\"\n",
    "#                      ]\n",
    "#     print(db.create_table('ratings', ','.join(ratings_table)))\n",
    "\n",
    "#     # 插入数据\n",
    "#     for i in range(len(ratings)):\n",
    "#         table_values = list(ratings.loc[i][:].values)\n",
    "#         print(db.insert('ratings', values=table_values, keys=ratings_title))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection Successful.  Database Version: < 5.7.31 >\n",
      "\n",
      "Table < movies >  Count: < 3883 >  Information:\n",
      "+---------+------------------+------+-----+---------+-------+\n",
      "| Field   |       Type       | Null | Key | Default | Extra |\n",
      "+---------+------------------+------+-----+---------+-------+\n",
      "| MovieID | int(10) unsigned |  NO  | PRI |   None  |       |\n",
      "| Title   |   varchar(100)   |  NO  |     |   None  |       |\n",
      "| Genres  |   varchar(100)   |  NO  |     |   None  |       |\n",
      "+---------+------------------+------+-----+---------+-------+\n",
      "\n",
      "Table < ratings >  Count: < 1000209 >  Information:\n",
      "+------------+------------------+------+-----+---------+-------+\n",
      "| Field      |       Type       | Null | Key | Default | Extra |\n",
      "+------------+------------------+------+-----+---------+-------+\n",
      "| UserID     | int(10) unsigned | YES  |     |   None  |       |\n",
      "| MovieID    | int(10) unsigned | YES  |     |   None  |       |\n",
      "| ratings    | int(10) unsigned | YES  |     |   None  |       |\n",
      "| timestamps | int(10) unsigned | YES  |     |   None  |       |\n",
      "+------------+------------------+------+-----+---------+-------+\n",
      "\n",
      "Table < users >  Count: < 6040 >  Information:\n",
      "+--------------+---------------------+------+-----+---------+-------+\n",
      "| Field        |         Type        | Null | Key | Default | Extra |\n",
      "+--------------+---------------------+------+-----+---------+-------+\n",
      "| UserID       |   int(10) unsigned  |  NO  | PRI |   None  |       |\n",
      "| Gender       |       char(1)       |  NO  |     |   None  |       |\n",
      "| Age          | tinyint(3) unsigned | YES  |     |   None  |       |\n",
      "| OccupationID | tinyint(3) unsigned | YES  |     |   None  |       |\n",
      "| ZipCode      |     varchar(20)     | YES  |     |   None  |       |\n",
      "+--------------+---------------------+------+-----+---------+-------+\n",
      "\n",
      "VERSION   : (('5.7.31',),)\n",
      "DATABASE  : (('MoviesLen',),)\n",
      "USER      : (('root@192.168.217.253',),)\n"
     ]
    }
   ],
   "source": [
    "# 连接数据库\n",
    "db = MySQL(host='localhost', port=3306, user='root', password='123456', database=\"MoviesLen\")\n",
    "\n",
    "# 创建表格\n",
    "# create_table(db)\n",
    "# ############################ 数据导入 （通过数据库软件或命令行导入）\n",
    "# use MoviesLen;\n",
    "\n",
    "# LOAD DATA LOCAL INFILE 'D:\\\\Data\\\\ml-1m\\\\users.dat' INTO TABLE users \n",
    "# FIELDS TERMINATED BY '::'\n",
    "# LINES TERMINATED BY '\\n';\n",
    "\n",
    "# LOAD DATA LOCAL INFILE 'D:\\\\Data\\\\ml-1m\\\\movies.dat' INTO TABLE movies \n",
    "# FIELDS TERMINATED BY '::'\n",
    "# LINES TERMINATED BY '\\n';\n",
    "\n",
    "# LOAD DATA LOCAL INFILE 'D:\\\\Data\\\\ml-1m\\\\ratings.dat' INTO TABLE ratings \n",
    "# FIELDS TERMINATED BY '::'\n",
    "# LINES TERMINATED BY '\\n';\n",
    "\n",
    "\n",
    "print(db.desc_table())\n",
    "\n",
    "    \n",
    "# SELECT VERSION( )\t服务器版本信息\n",
    "# SELECT DATABASE( )\t当前数据库名 (或者返回空)\n",
    "# SELECT USER( )\t当前用户名\n",
    "\n",
    "print('{:10s}: {}'.format('VERSION', db.select_sql(\"SELECT VERSION()\")))\n",
    "print('{:10s}: {}'.format('DATABASE', db.select_sql(\"SELECT DATABASE()\")))\n",
    "print('{:10s}: {}'.format('USER', db.select_sql(\"SELECT USER()\")))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 执行顺序\n",
    "where、group by、having、order by [ASC|DESC]、limit  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "'Use BINARY:'\n",
      "()\n",
      "(('Toys (1992)',),)\n",
      "(('History of the World: Part I (1981)',), ('American History X (1998)',))\n",
      "(('Toy Story (1995)', \"Animation|Children's|Comedy\"),\n",
      " ('Toy Story 2 (1999)', \"Animation|Children's|Comedy\"),\n",
      " (\"We're Back! A Dinosaur's Story (1993)\", \"Animation|Children's\"))\n",
      "((619, 'Ed (1996)', 3),\n",
      " (755, 'Kim (1950)', 4),\n",
      " (1217, 'Ran (1985)', 4),\n",
      " (1260, 'M (1931)', 2),\n",
      " (1609, '187 (1997)', 4),\n",
      " (1705, 'Guy (1996)', 4),\n",
      " (1878, 'Woo (1998)', 4),\n",
      " (1921, 'Pi (1998)', 3),\n",
      " (2104, 'Tex (1982)', 4),\n",
      " (2188, '54 (1998)', 3),\n",
      " (2505, '8MM (1999)', 4),\n",
      " (2580, 'Go (1999)', 3),\n",
      " (2603, 'Nô (1998)', 3),\n",
      " (2797, 'Big (1988)', 4),\n",
      " (3386, 'JFK (1991)', 4),\n",
      " (3467, 'Hud (1963)', 4),\n",
      " (3763, 'F/X (1986)', 4))\n",
      "\n",
      "\n",
      "Use DISTINCT:\n",
      "((1,), (56,), (25,), (45,), (50,), (35,), (18,))\n",
      "\n",
      "\n",
      "Use UNION:\n",
      "\n",
      "(('Akira (1988)',),\n",
      " ('Fantastic Planet, The (La Planète sauvage) (1973)',),\n",
      " ('Ghost in the Shell (Kokaku kidotai) (1995)',),\n",
      " ('Heavy (1995)',),\n",
      " ('Heavy Metal (1981)',),\n",
      " ('Heavyweights (1994)',),\n",
      " ('Lord of the Rings, The (1978)',),\n",
      " ('Time Masters (Les Maîtres du Temps) (1982)',),\n",
      " ('Titan A.E. (2000)',),\n",
      " ('Transformers: The Movie, The (1986)',))\n",
      "\n",
      "\n",
      "Use ORDER BY:\n",
      "\n",
      "((\"Action|Children's\", 'Mighty Morphin Power Rangers: The Movie (1995)'),\n",
      " (\"Adventure|Children's\", 'Amazing Panda Adventure, The (1995)'),\n",
      " (\"Adventure|Children's\", 'Casper (1995)'),\n",
      " (\"Adventure|Children's\", 'Far From Home: The Adventures of Yellow Dog (1995)'),\n",
      " (\"Adventure|Children's\", 'Gold Diggers: The Secret of Bear Mountain (1995)'),\n",
      " (\"Adventure|Children's\", 'Tom and Huck (1995)'),\n",
      " (\"Adventure|Children's|Comedy|Fantasy|Romance\",\n",
      "  \"Kid in King Arthur's Court, A (1995)\"),\n",
      " (\"Adventure|Children's|Drama\", 'Free Willy 2: The Adventure Home (1995)'),\n",
      " (\"Adventure|Children's|Fantasy\", 'Indian in the Cupboard, The (1995)'),\n",
      " (\"Adventure|Children's|Fantasy\", 'Jumanji (1995)'),\n",
      " (\"Adventure|Children's|Fantasy\", 'Kids of the Round Table (1995)'),\n",
      " (\"Animation|Children's\", 'Balto (1995)'),\n",
      " (\"Animation|Children's\", 'Gumby: The Movie (1995)'),\n",
      " (\"Animation|Children's\",\n",
      "  'Land Before Time III: The Time of the Great Giving (1995)'),\n",
      " (\"Animation|Children's|Comedy\", 'Toy Story (1995)'),\n",
      " (\"Animation|Children's|Comedy|Romance\", 'Goofy Movie, A (1995)'),\n",
      " (\"Animation|Children's|Musical|Romance\", 'Pocahontas (1995)'),\n",
      " (\"Children's\", 'Baby-Sitters Club, The (1995)'),\n",
      " (\"Children's|Comedy\", 'Big Green, The (1995)'),\n",
      " (\"Children's|Comedy|Drama\", 'Babe (1995)'),\n",
      " (\"Children's|Drama\", 'Fluke (1995)'),\n",
      " (\"Children's|Drama\", 'Little Princess, A (1995)'))\n",
      "\n",
      "\n",
      "Use ORDER BY:\n",
      "\n",
      "((1, 222), (18, 1103), (25, 2096), (35, 1193), (45, 550), (50, 496), (56, 380))\n",
      "\n",
      "\n",
      "Group BY     ORDER BY:\n",
      "\n",
      "((283, 27, Decimal('4.9630')),\n",
      " (2339, 23, Decimal('4.9565')),\n",
      " (3324, 21, Decimal('4.9048')),\n",
      " (3902, 165, Decimal('4.8909')),\n",
      " (446, 51, Decimal('4.8431')),\n",
      " (447, 37, Decimal('4.8378')),\n",
      " (4649, 33, Decimal('4.8182')),\n",
      " (4634, 102, Decimal('4.8137')),\n",
      " (1131, 103, Decimal('4.7961')),\n",
      " (4925, 42, Decimal('4.7619')))\n",
      "\n",
      "\n",
      "INNER  JOIN:\n",
      "\n",
      "((7, 35, 'M', 31, Decimal('4.3226')),\n",
      " (36, 25, 'M', 351, Decimal('4.1994')),\n",
      " (46, 18, 'M', 41, Decimal('4.2195')),\n",
      " (53, 25, 'M', 684, Decimal('4.2368')),\n",
      " (65, 35, 'M', 121, Decimal('4.3471')),\n",
      " (67, 50, 'F', 64, Decimal('4.2969')),\n",
      " (81, 25, 'F', 86, Decimal('4.4186')),\n",
      " (86, 1, 'F', 48, Decimal('4.3125')),\n",
      " (91, 35, 'M', 44, Decimal('4.7045')),\n",
      " (97, 35, 'F', 154, Decimal('4.4740')))\n",
      "\n",
      "\n",
      "LEFT  JOIN:\n",
      "\n",
      "((1, 1, 'F', None, None),\n",
      " (2, 56, 'M', None, None),\n",
      " (3, 25, 'M', None, None),\n",
      " (4, 45, 'M', None, None),\n",
      " (5, 25, 'M', None, None),\n",
      " (6, 50, 'F', None, None),\n",
      " (7, 35, 'M', 31, Decimal('4.3226')),\n",
      " (8, 25, 'M', None, None),\n",
      " (9, 25, 'M', None, None),\n",
      " (10, 35, 'F', None, None),\n",
      " (11, 25, 'F', None, None),\n",
      " (12, 25, 'M', None, None),\n",
      " (13, 45, 'M', None, None),\n",
      " (14, 35, 'M', None, None),\n",
      " (15, 25, 'M', None, None),\n",
      " (16, 35, 'F', None, None),\n",
      " (17, 50, 'M', None, None),\n",
      " (18, 18, 'F', None, None),\n",
      " (19, 1, 'M', None, None),\n",
      " (20, 25, 'M', None, None))\n",
      "\n",
      "\n",
      "正则表达式  REGEXP:\n",
      "\n",
      "((252, 'I.Q. (1994)', 'Comedy|Romance'),\n",
      " (276, 'Milk Money (1994)', 'Comedy|Romance'),\n",
      " (287, 'Nina Takes a Lover (1994)', 'Comedy|Romance'),\n",
      " (289, 'Only You (1994)', 'Comedy|Romance'),\n",
      " (342, \"Muriel's Wedding (1994)\", 'Comedy|Romance'),\n",
      " (357, 'Four Weddings and a Funeral (1994)', 'Comedy|Romance'),\n",
      " (378, 'Speechless (1994)', 'Comedy|Romance'),\n",
      " (417, 'Barcelona (1994)', 'Comedy|Romance'),\n",
      " (440, 'Dave (1993)', 'Comedy|Romance'),\n",
      " (444, 'Even Cowgirls Get the Blues (1993)', 'Comedy|Romance'),\n",
      " (447, 'Favor, The (1994)', 'Comedy|Romance'),\n",
      " (471, 'Hudsucker Proxy, The (1994)', 'Comedy|Romance'),\n",
      " (497, 'Much Ado About Nothing (1993)', 'Comedy|Romance'),\n",
      " (499, 'Mr. Wonderful (1993)', 'Comedy|Romance'),\n",
      " (539, 'Sleepless in Seattle (1993)', 'Comedy|Romance'),\n",
      " (550, 'Threesome (1994)', 'Comedy|Romance'),\n",
      " (566, 'Naked in New York (1994)', 'Comedy|Romance'),\n",
      " (1265, 'Groundhog Day (1993)', 'Comedy|Romance'),\n",
      " (1441, 'Benny & Joon (1993)', 'Comedy|Romance'))\n"
     ]
    }
   ],
   "source": [
    "from pprint import pprint\n",
    "\n",
    "#  查询  使用了 BINARY 关键字，区分大小写\n",
    "pprint('Use BINARY:')\n",
    "pprint(db.select_sql(\"select Title FROM movies where BINARY Title = 'toys (1992)';\"))\n",
    "pprint(db.select_sql(\"select Title FROM movies where BINARY Title = 'Toys (1992)';\"))\n",
    "pprint(db.select_sql(\"select Title FROM movies where BINARY Title LIKE '%story%';\"))\n",
    "pprint(db.select_sql(\"select Title, Genres FROM movies where BINARY Title LIKE '%Story%' AND Genres LIKE '%Animation%';\"))\n",
    "pprint(db.select_sql(\"SELECT MovieID, Title, LENGTH(Title)-6 FROM MoviesLen.movies Where LENGTH(Title)-6 < 5;\"))\n",
    "\n",
    "\n",
    "#  DISTINCT  读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据\n",
    "print('\\n\\nUse DISTINCT:')\n",
    "pprint(db.select_sql(\"SELECT DISTINCT Age From MoviesLen.users;\"))\n",
    " \n",
    "\n",
    "\n",
    "# UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）\n",
    "# UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）\n",
    "sql_code = \"\"\"\n",
    "select Title FROM movies where Title LIKE 'Heavy%'\n",
    "UNION \n",
    "select Title FROM movies where Genres LIKE '%Animation%' AND Genres LIKE '%Sci-Fi%'\n",
    "ORDER BY Title ;\n",
    "\"\"\"\n",
    "print('\\n\\nUse UNION:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# ORDER BY 排序    ASC 或 DESC 关键字来设置查询结果是按升序或降序排列\n",
    "sql_code = \"\"\"\n",
    "select Genres as ord_1, Title as ord_2 FROM movies \n",
    "where Title LIKE '%(1995)%' AND Genres LIKE \"%Children's%\"\n",
    "ORDER BY Genres, Title;\n",
    "\"\"\"\n",
    "print('\\n\\nUse ORDER BY:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# Group BY 分组\n",
    "sql_code = \"\"\"\n",
    "Select age, COUNT(age) as age_num From  MoviesLen.users Group by age\n",
    "\"\"\"\n",
    "print('\\n\\nUse ORDER BY:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# Group BY ORDER BY\n",
    "sql_code = \"\"\"\n",
    "Select UserID, COUNT(ratings) as r_cou, AVG(ratings) as r_avg \n",
    "FROM MoviesLen.ratings where UserID < 20000 \n",
    "Group by UserID\n",
    "ORDER by r_avg DESC \n",
    "limit 10\n",
    "\"\"\"\n",
    "print('\\n\\nGroup BY     ORDER BY:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。\n",
    "# LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。\n",
    "# RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录\n",
    "\n",
    "# INNER  JOIN\n",
    "sql_code = \"\"\"\n",
    "SELECT a.UserID, a.Age, a.Gender, b.r_cou, b.r_avg FROM MoviesLen.users a\n",
    "INNER  JOIN\n",
    "\t(Select UserID, COUNT(ratings) as r_cou, AVG(ratings) as r_avg \n",
    "\tFROM MoviesLen.ratings where UserID < 100 \n",
    "\tGroup by UserID\n",
    "\tORDER by r_avg DESC \n",
    "\tlimit 10) b\n",
    "ON\n",
    "\ta.UserID = b.UserID\n",
    "ORDER BY a.UserID \n",
    "LIMIT 20\n",
    "\"\"\"\n",
    "print('\\n\\nINNER  JOIN:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# LEFT  JOIN\n",
    "sql_code = \"\"\"\n",
    "SELECT a.UserID, a.Age, a.Gender, b.r_cou, b.r_avg FROM MoviesLen.users a\n",
    "LEFT JOIN\n",
    "\t(Select UserID, COUNT(ratings) as r_cou, AVG(ratings) as r_avg \n",
    "\tFROM MoviesLen.ratings where UserID < 100 \n",
    "\tGroup by UserID\n",
    "\tORDER by r_avg DESC \n",
    "\tlimit 10) b\n",
    "ON\n",
    "\ta.UserID = b.UserID\n",
    "ORDER BY a.UserID \n",
    "LIMIT 20\n",
    "\"\"\"\n",
    "print('\\n\\nLEFT  JOIN:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# 正则表达式  REGEXP  '.*199[43].*  1994 或 1993    '^Comedy.Romance$'以Comedy开头Romance结尾\n",
    "sql_code = \"\"\"\n",
    "SELECT * FROM MoviesLen.movies  WHERE Title REGEXP '.*199[43].*' AND  Genres REGEXP '^Comedy.Romance$' ;\n",
    "\"\"\"\n",
    "print('\\n\\n正则表达式  REGEXP:\\n')\n",
    "pprint(db.select_sql(sql_code))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "TEMPORARY TABLE:\n",
      "\n",
      "()\n",
      "\n",
      "\n",
      "查询临时表:\n",
      "\n",
      "((252, 'I.Q. (1994)', 'Comedy|Romance'),\n",
      " (276, 'Milk Money (1994)', 'Comedy|Romance'),\n",
      " (287, 'Nina Takes a Lover (1994)', 'Comedy|Romance'),\n",
      " (289, 'Only You (1994)', 'Comedy|Romance'),\n",
      " (342, \"Muriel's Wedding (1994)\", 'Comedy|Romance'),\n",
      " (357, 'Four Weddings and a Funeral (1994)', 'Comedy|Romance'),\n",
      " (378, 'Speechless (1994)', 'Comedy|Romance'),\n",
      " (417, 'Barcelona (1994)', 'Comedy|Romance'),\n",
      " (440, 'Dave (1993)', 'Comedy|Romance'),\n",
      " (444, 'Even Cowgirls Get the Blues (1993)', 'Comedy|Romance'),\n",
      " (447, 'Favor, The (1994)', 'Comedy|Romance'),\n",
      " (471, 'Hudsucker Proxy, The (1994)', 'Comedy|Romance'),\n",
      " (497, 'Much Ado About Nothing (1993)', 'Comedy|Romance'),\n",
      " (499, 'Mr. Wonderful (1993)', 'Comedy|Romance'),\n",
      " (539, 'Sleepless in Seattle (1993)', 'Comedy|Romance'),\n",
      " (550, 'Threesome (1994)', 'Comedy|Romance'),\n",
      " (566, 'Naked in New York (1994)', 'Comedy|Romance'),\n",
      " (1265, 'Groundhog Day (1993)', 'Comedy|Romance'),\n",
      " (1441, 'Benny & Joon (1993)', 'Comedy|Romance'))\n",
      "\n",
      "\n",
      "删除临时表:\n",
      "\n",
      "()\n",
      "\n",
      "\n",
      "查询临时表:\n",
      "\n",
      "Error: \n",
      "\n",
      "SELECT * FROM temp_table;\n",
      "\n",
      "(1146, \"Table 'MoviesLen.temp_table' doesn't exist\")\n"
     ]
    }
   ],
   "source": [
    "# 临时表   临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。\n",
    "# 根据查询结果创建临时表\n",
    "sql_code = \"\"\"\n",
    "CREATE TEMPORARY TABLE temp_table AS\n",
    "(\n",
    "    SELECT * FROM MoviesLen.movies  WHERE Title REGEXP '.*199[43].*' AND  Genres REGEXP '^Comedy.Romance$'\n",
    ");\n",
    "\"\"\"\n",
    "print('\\n\\nTEMPORARY TABLE:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# 查询临时表\n",
    "sql_code = \"\"\"\n",
    "SELECT * FROM temp_table;\n",
    "\"\"\"\n",
    "print('\\n\\n查询临时表:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# 删除临时表\n",
    "sql_code = \"\"\"\n",
    "DROP TABLE temp_table;\n",
    "\"\"\"\n",
    "print('\\n\\n删除临时表:\\n')\n",
    "pprint(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# 查询临时表\n",
    "sql_code = \"\"\"\n",
    "SELECT * FROM temp_table;\n",
    "\"\"\"\n",
    "print('\\n\\n查询临时表:\\n')\n",
    "print(db.select_sql(sql_code))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "FORMAT:\n",
      "(('4.19',), ('3.71',), ('3.90',), ('4.19',), ('3.15',), ('3.90',), ('4.32',), ('3.88',), ('3.74',))\n",
      "\n",
      "\n",
      "mod:\n",
      "((100, 'M', 35, 17, '95401'), (200, 'F', 18, 4, '84321'), (300, 'M', 25, 7, '78664'), (400, 'F', 18, 3, '55422'), (500, 'F', 18, 2, '55105'), (600, 'M', 35, 17, '66209'), (700, 'M', 50, 17, '14650'), (800, 'F', 35, 12, '72032'), (900, 'F', 56, 13, '90066'), (1000, 'F', 25, 6, '90027'))\n"
     ]
    }
   ],
   "source": [
    "# 函数  https://www.runoob.com/mysql/mysql-functions.html\n",
    "\n",
    "#  FORMAT\t函数可以将数字 x 进行格式化 \"#,###.##\", 将 x 保留到小数点后 n 位，最后一位四舍五入。    SELECT FORMAT(250500.5634, 2);\n",
    "sql_code = \"\"\"\n",
    "SELECT FORMAT(AVG(ratings),2) FROM MoviesLen.ratings where UserID <10 GROUP by UserID ;\n",
    "\"\"\"\n",
    "print('\\n\\nFORMAT:')\n",
    "print(db.select_sql(sql_code))\n",
    "\n",
    "\n",
    "# 取余数\n",
    "sql_code = \"\"\"\n",
    "SELECT * FROM users where MOD(UserID,100) = 0  LIMIT 10;\n",
    "\"\"\"\n",
    "print('\\n\\nmod:')\n",
    "print(db.select_sql(sql_code))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      " 男女平均打分:\n",
      "(('F', Decimal('3.6204')), ('M', Decimal('3.5689')))\n"
     ]
    }
   ],
   "source": [
    "# 男女平均打分\n",
    "sql_code = \"\"\"\n",
    "SELECT j.Gender as Gender, AVG(j.ratings) as MeanScore FROM \n",
    "\t(\n",
    "\tSELECT u.Gender, r.ratings FROM ratings r \n",
    "\tLEFT JOIN\n",
    "\t\t(SELECT UserID, Gender FROM users) u \n",
    "\tON r.UserID = u.UserID \t\n",
    "\t) j\n",
    "GROUP BY j.Gender \n",
    "\"\"\"\n",
    "print('\\n\\n 男女平均打分:')\n",
    "pprint(db.select_sql(sql_code))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      " 评分次数大于50 的电影的平均得分排序:\n",
      "(('Sanjuro (1962)', '4.61', 69),\n",
      " ('Seven Samurai (The Magnificent Seven) (Shichinin no samurai) (1954)',\n",
      "  '4.56',\n",
      "  628),\n",
      " ('Shawshank Redemption, The (1994)', '4.55', 2227),\n",
      " ('Close Shave, A (1995)', '4.52', 657),\n",
      " ('Godfather, The (1972)', '4.52', 2223),\n",
      " ('Usual Suspects, The (1995)', '4.52', 1783),\n",
      " (\"Schindler's List (1993)\", '4.51', 2304),\n",
      " ('Wrong Trousers, The (1993)', '4.51', 882),\n",
      " ('Sunset Blvd. (a.k.a. Sunset Boulevard) (1950)', '4.49', 470),\n",
      " ('Raiders of the Lost Ark (1981)', '4.48', 2514),\n",
      " ('Rear Window (1954)', '4.48', 1050),\n",
      " ('Paths of Glory (1957)', '4.47', 230),\n",
      " ('Third Man, The (1949)', '4.45', 480),\n",
      " ('Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb (1963)',\n",
      "  '4.45',\n",
      "  1367),\n",
      " ('Star Wars: Episode IV - A New Hope (1977)', '4.45', 2991),\n",
      " ('To Kill a Mockingbird (1962)', '4.43', 928),\n",
      " ('Wallace & Gromit: The Best of Aardman Animation (1996)', '4.43', 438),\n",
      " ('Double Indemnity (1944)', '4.42', 551),\n",
      " ('Casablanca (1942)', '4.41', 1669),\n",
      " ('Sixth Sense, The (1999)', '4.41', 2459))\n"
     ]
    }
   ],
   "source": [
    "# 评分次数大于50 的电影的平均得分排序\n",
    "sql_code = \"\"\"\n",
    "SELECT j.Title as Title, FORMAT(AVG(j.ratings),2) as MeanScore , COUNT(j.ratings) as Num FROM \n",
    "\t(\n",
    "\tSELECT m.Title, r.ratings FROM ratings r \n",
    "\tLEFT JOIN\n",
    "\t\t(SELECT MovieID , Title FROM movies) m \n",
    "\tON r.MovieID = m.MovieID \n",
    "\t) j\n",
    "GROUP BY j.Title \n",
    "HAVING Num > 50\n",
    "ORDER BY MeanScore DESC \n",
    "LIMIT 20\n",
    "\"\"\"\n",
    "print('\\n\\n 评分次数大于50 的电影的平均得分排序:')\n",
    "pprint(db.select_sql(sql_code))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      " 观看人数大于 50，男女评分差异排序:\n",
      "((1255, 'Bad Taste (1987)', 1.7699999999999998, 44, Decimal('54')),\n",
      " (1241, 'Braindead (1992)', 1.68, 52, Decimal('70')),\n",
      " (2084, 'Newsies (1992)', 1.19, 21, Decimal('93')),\n",
      " (1837, 'Odd Couple II, The (1998)', 1.15, 42, Decimal('64')),\n",
      " (3375, 'Destination Moon (1950)', 1.02, 51, Decimal('65')),\n",
      " (2275, 'Six-String Samurai (1998)', 0.98, 55, Decimal('71')),\n",
      " (2152, 'Air Bud: Golden Receiver (1998)', 0.97, 33, Decimal('69')),\n",
      " (2514, 'Pet Sematary II (1992)', 0.97, 68, Decimal('116')),\n",
      " (2035, \"Blackbeard's Ghost (1968)\", 0.9000000000000004, 21, Decimal('57')),\n",
      " (1978,\n",
      "  'Friday the 13th Part V: A New Beginning (1985)',\n",
      "  0.8999999999999999,\n",
      "  92,\n",
      "  Decimal('114')),\n",
      " (3718, 'American Pimp (1999)', 0.8799999999999999, 43, Decimal('61')),\n",
      " (3658, 'Quatermass and the Pit (1967)', 0.8799999999999999, 49, Decimal('69')),\n",
      " (3372, 'Bridge at Remagen, The (1969)', 0.8700000000000001, 48, Decimal('56')),\n",
      " (1523,\n",
      "  'Truth or Consequences, N.M. (1997)',\n",
      "  0.8700000000000001,\n",
      "  41,\n",
      "  Decimal('57')),\n",
      " (15, 'Cutthroat Island (1995)', 0.8600000000000003, 106, Decimal('146')),\n",
      " (3165, 'Boiling Point (1993)', 0.8500000000000001, 51, Decimal('61')),\n",
      " (3695,\n",
      "  'Toxic Avenger Part III: The Last Temptation of Toxie, The (1989)',\n",
      "  0.8400000000000001,\n",
      "  49,\n",
      "  Decimal('53')),\n",
      " (1088, 'Dirty Dancing (1987)', 0.8300000000000001, 105, Decimal('687')),\n",
      " (1592, 'Air Bud (1997)', 0.8300000000000001, 42, Decimal('112')),\n",
      " (469,\n",
      "  'House of the Spirits, The (1993)',\n",
      "  0.8200000000000003,\n",
      "  21,\n",
      "  Decimal('69')))\n"
     ]
    }
   ],
   "source": [
    "# 观看人数大于 50，男女评分差异排序\n",
    "sql_code = \"\"\"\n",
    "SELECT MovieID, Title, (max(MeanScore) - min(MeanScore)) as s_diff, (max(r_num) - min(r_num)) as r_diff, SUM(r_num) as r_sum\n",
    "FROM \n",
    "\t(\n",
    "\t\tSELECT MovieID, Title,  Gender, FORMAT(AVG(ratings),2) as MeanScore, COUNT(ratings) as r_num FROM \n",
    "\t\t(\n",
    "\t\t\tSELECT ru.Gender, ru.MovieID, ru.ratings, m.Title FROM \n",
    "\t\t\t\t(\n",
    "\t\t\t\tSELECT u.Gender, r.MovieID, r.ratings FROM ratings r \n",
    "\t\t\t\tLEFT JOIN\n",
    "\t\t\t\t\t(SELECT UserID, Gender FROM users) u \n",
    "\t\t\t\tON r.UserID = u.UserID \t\n",
    "\t\t\t\t) ru\n",
    "\t\t\tLEFT JOIN\n",
    "\t\t\t\t(SELECT MovieID, Title FROM movies) m \n",
    "\t\t\tON ru.MovieID = m.MovieID \t\n",
    "\t\t) rum\n",
    "\t\tGROUP BY rum.MovieID, rum.Gender\n",
    "\t\tORDER BY rum.MovieID, rum.Gender\n",
    "\t) temp_table\t\n",
    "GROUP BY MovieID, Title\n",
    "HAVING r_sum > 50\n",
    "ORDER BY s_diff DESC\n",
    "LIMIT 20\n",
    "\"\"\"\n",
    "print('\\n\\n 观看人数大于 50，男女评分差异排序:')\n",
    "pprint(db.select_sql(sql_code))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PY36-tf12",
   "language": "python",
   "name": "py36_b"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
